﻿CREATE TABLE [dbo].[Customer] (
    [CustomerId]      INT           IDENTITY (1, 1) NOT NULL,
    [CustomerNumber]  AS right('00000' + cast([CustomerId] as varchar(6)), 6) PERSISTED,
    [FirstName]       VARCHAR (50)  NOT NULL,
    [LastName]        VARCHAR (50)  NOT NULL,
    [MiddleInitial]   VARCHAR       NULL,
    [Furigana]        NVARCHAR (50) NULL,
	[DateOfBirth]     DATE          NOT NULL,
    [HomePhone]       VARCHAR (15)  NULL,
    [CellPhone]       VARCHAR (15)  NULL,
    [WorkPhone]       VARCHAR (15)  NOT NULL,
    [Email]           VARCHAR (128) NOT NULL,
    [Email2]          VARCHAR (128) NULL,
    [HomeAddress]     VARCHAR (256) NOT NULL,
    [BuildingName]    VARCHAR (256) NOT NULL,
    [WorkUnit]        VARCHAR (50)  NOT NULL,
    [SecretQuestion]  VARCHAR (128) NULL,
    [SecretAnswer]    VARCHAR (128) NULL,
    [PIN]             VARCHAR (10)  NULL,
    [BankId]          UNIQUEIDENTIFIER NULL,
    [AccountNumber]   VARCHAR(15)   NOT NULL, 
    [AuthorizedRep]   VARCHAR (128) NULL,
    [AuthorizedPhone] VARCHAR (128) NULL,
    [Status]          VARCHAR (50)  NOT NULL,
    [DateCreated]  DATETIME         DEFAULT (getdate()) NOT NULL,
    [CreatedBy]    VARCHAR (50)     DEFAULT (suser_sname()) NOT NULL,
    [DateModified] DATE             NULL,
    [ModifiedBy]   VARCHAR (50)     NULL,
	CONSTRAINT [FK_Customer_Bank] FOREIGN KEY ([BankId]) REFERENCES [dbo].[Bank] ([BankId]) ON DELETE SET NULL ON UPDATE CASCADE, 
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([CustomerId] ASC)
);

GO

CREATE INDEX [IX_Customer_BankId] ON [dbo].[Customer] ([BankId])

GO



